package com.infomanage.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.infomanage.bean.FieldBean;
import com.infomanage.bean.PaginationBean;
import com.infomanage.bean.SearchBean;
import com.infomanage.bean.TimeBean;
import com.infomanage.enums.RoleType;
import com.infomanage.exception.DaoException;
import com.infomanage.po.Department;
import com.infomanage.po.HibernateSessionFactory;
import com.jiuqi.util.OrderGenerator;

public class BaseDAO<E> {

	public void add(E entity) throws DaoException {
		Session session = HibernateSessionFactory.getSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.save(entity);
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	@SuppressWarnings("unchecked")
	public void remove(Class<?> clazz, int id) throws DaoException {
		Session session = HibernateSessionFactory.getSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			E e = (E) session.load(clazz, new Integer(id));
			session.delete(e);
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	};

	public void removeByField(Class<?> clazz, FieldBean fieldBean) throws DaoException {
		Session session = HibernateSessionFactory.getSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			Query query = session.createQuery("delete from " + clazz.getSimpleName() + " where " + fieldBean.getName()
					+ "=" + fieldBean.getValue());
			query.executeUpdate();
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	};

	public void modify(E entity) throws DaoException {
		Session session = HibernateSessionFactory.getSession();
		Transaction tx = null;
		try {
			tx = session.beginTransaction();
			session.update(entity);
			tx.commit();
		} catch (Exception e) {
			tx.rollback();
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	};

	@SuppressWarnings("unchecked")
	public E find(Class<?> clazz, int id) throws DaoException {
		Session session = HibernateSessionFactory.getSession();
		E entity = null;
		try {
			entity = (E) session.get(clazz, new Integer(id));
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
		return entity;
	};

	@SuppressWarnings("unchecked")
	public List<E> findAll(Class<?> clazz) throws DaoException {
		Session session = HibernateSessionFactory.getSession();
		try {
			return session.createQuery("from " + clazz.getSimpleName()).list();
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	@SuppressWarnings("unchecked")
	// TODO 增加排序
	public List<E> findByField(Class<?> clazz, FieldBean fieldBean) throws DaoException {
		Session session = HibernateSessionFactory.getSession();
		try {
			// String hsql = "from " + clazz.getSimpleName() + " where " +
			// fieldBean.getName() + "=?";
			// Query query = (Query) session.createQuery(hsql);
			// query.setInteger(1, (Integer)fieldBean.getValue());
			SearchBean searchBean = new SearchBean();
			searchBean.add(fieldBean);
			return findByFields(clazz, searchBean);
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	public List findAllMutilTable(Class<?>[] clazzs, Class<?> masterClazz, String[] joinFields, String[] masterFields)
			throws DaoException {
		return findByFields(clazzs, masterClazz, joinFields, masterFields, null);
	}

	// 联表查询
	@SuppressWarnings("unchecked")
	public List findByFields(Class<?>[] clazzs, Class<?> masterClazz, String[] joinFields, String[] masterFields,
			SearchBean searchBean) throws DaoException {

		// 权限
		boolean hasDept = false;
		if (searchBean.getAuthority() != null) {
			for (int i = 0; i < clazzs.length; i++) {
				if (clazzs[i].getSimpleName().equals(Department.class.getSimpleName())) {
					hasDept = true;
					break;
				}
			}
		}
		Integer[] deptIds = null;
		// 当存在时进行权限设置
		if (searchBean.getAuthority() != null && hasDept) {
			RoleType role = searchBean.getAuthority().getRole();
			int deptId = searchBean.getAuthority().getDeptId();
			if (role.value() == RoleType.ZDADMIN.value() || role.value() == RoleType.GENERALUSER.value()) {
				deptIds = findChildDept(deptId);
			}
		}

		Session session = HibernateSessionFactory.getSession();
		List<E> result = null;
		try {
			String hsql = buildHSQL(clazzs, masterClazz, joinFields, masterFields, searchBean, false, deptIds);
			Query query = (Query) session.createQuery(hsql.toString());
			if (searchBean != null) {
				for (int i = 0; i < searchBean.getFields().length; i++) {
					FieldBean field = searchBean.getFields()[i];
					if (field.getValue() instanceof String) {
						if (field.isLike()) {
							query.setString(i, "%" + (String) field.getValue() + "%");
						} else {
							query.setString(i, (String) field.getValue());
						}
					} else if (field.getValue() instanceof Integer) {
						query.setInteger(i, (Integer) field.getValue());
					}
				}
				int index = searchBean.getFields().length;
				if (searchBean.getBeginTimeValue() != null) {
					query.setString(index, searchBean.getBeginTimeValue());
					index++;
				}
				if (searchBean.getEndTimeValue() != null) {
					query.setString(index, searchBean.getEndTimeValue());
					index++;
				}

				if (deptIds != null) {
					for (int j = 0; j < deptIds.length; j++) {
						query.setInteger(index + j, deptIds[j]);
					}
				}
			}

			// 分页
			if (searchBean != null) {
				PaginationBean page = searchBean.getPage();
				if (page != null) {
					query.setFirstResult((page.getCurrentPageNum() - 1) * PaginationBean.pageSize);
					query.setMaxResults(PaginationBean.pageSize);
				}
			}
			result = query.list();
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
		return result;
	}

	public int count(Class<?>[] clazzs, Class<?> masterClazz, String[] joinFields, String[] masterFields,
			SearchBean searchBean) {
		// 权限
		boolean hasDept = false;
		if (searchBean.getAuthority() != null) {
			for (int i = 0; i < clazzs.length; i++) {
				if (clazzs[i].getSimpleName().equals(Department.class.getSimpleName())) {
					hasDept = true;
					break;
				}
			}
		}
		Integer[] deptIds = null;
		// 当存在时进行权限设置
		if (searchBean.getAuthority() != null && hasDept) {
			RoleType role = searchBean.getAuthority().getRole();
			int deptId = searchBean.getAuthority().getDeptId();
			if (role.value() == RoleType.ZDADMIN.value() || role.value() == RoleType.GENERALUSER.value()) {
				try {
					deptIds = findChildDept(deptId);
				} catch (DaoException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		Session session = HibernateSessionFactory.getSession();
		try{
			String hsql = buildHSQL(clazzs, masterClazz, joinFields, masterFields, searchBean, true, deptIds);
			Query query = (Query) session.createQuery(hsql.toString());
			if (searchBean != null) {
				for (int i = 0; i < searchBean.getFields().length; i++) {
					FieldBean field = searchBean.getFields()[i];
					if (field.getValue() instanceof String) {
						if (field.isLike()) {
							query.setString(i, "%" + (String) field.getValue() + "%");
						} else {
							query.setString(i, (String) field.getValue());
						}
					} else if (field.getValue() instanceof Integer) {
						query.setInteger(i, (Integer) field.getValue());
					}
				}
				int index = searchBean.getFields().length;
				if (searchBean.getBeginTimeValue() != null) {
					query.setString(index, searchBean.getBeginTimeValue());
					index++;
				}
				if (searchBean.getEndTimeValue() != null) {
					query.setString(index, searchBean.getEndTimeValue());
					index++;
				}
				
				if (deptIds != null) {
					for (int j = 0; j < deptIds.length; j++) {
						query.setInteger(index + j, deptIds[j]);
					}
				}
			}
			return ((Long) query.uniqueResult()).intValue();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			HibernateSessionFactory.closeSession();
		}
		return 0;
	}

	public String buildHSQL(Class<?>[] clazzs, Class<?> masterClazz, String[] joinFields, String[] masterFields,
			SearchBean searchBean, boolean withCount, Integer[] deptIds) {
		StringBuffer hsql = new StringBuffer();
		if (withCount) {
			hsql.append(" select ");
			hsql.append(" count(*) ");
		}
		hsql.append(" from ");
		String[] alias = new String[clazzs.length + 1];
		for (int i = 0; i < alias.length; i++) {
			alias[i] = OrderGenerator.newOrder();
		}
		hsql.append(masterClazz.getSimpleName());
		hsql.append(" as " + alias[0]);
		for (int i = 0; i < clazzs.length; i++) {
			hsql.append(",");
			hsql.append(clazzs[i].getSimpleName());
			hsql.append(" as " + alias[i + 1]);
		}
		hsql.append(" where ");
		for (int i = 0; i < joinFields.length; i++) {
			if (i != 0) {
				hsql.append(" and ");
			}
			hsql.append(alias[0] + "." + masterFields[i] + "=" + alias[i + 1] + "." + joinFields[i]);
		}
		if (searchBean != null) {
			for (int i = 0; i < searchBean.getFields().length; i++) {
				hsql.append(" and ");
				if (searchBean.getFields()[i].isLike()) {
					hsql.append(alias[0] + "." + searchBean.getFields()[i].getName() + " like ?");
				} else {
					hsql.append(alias[0] + "." + searchBean.getFields()[i].getName() + " = ?");
				}
			}
			if (searchBean.getBeginTimeName() != null) {
				if (searchBean.getBeginTimeValue() != null) {
					hsql.append(" and ");
					hsql.append(alias[0] + "." + searchBean.getBeginTimeName() + " >= ?");
				}
			}
			if (searchBean.getEndTimeName() != null) {
				if (searchBean.getEndTimeValue() != null) {
					hsql.append(" and ");
					hsql.append(alias[0] + "." + searchBean.getEndTimeName() + " <= ?");
				}
			}
			// 权限
			if (searchBean.getAuthority() != null) {
				RoleType role = searchBean.getAuthority().getRole();
				if (role.value() == RoleType.ZDADMIN.value() || role.value() == RoleType.GENERALUSER.value()) {
					for (int i = 0; i < clazzs.length; i++) {
						if (clazzs[i].getSimpleName().equals(Department.class.getSimpleName())) {
							hsql.append(" and ");
							hsql.append(alias[i + 1] + ".deptId in(");
							if (deptIds != null) {
								for (int j = 0; j < deptIds.length; j++) {
									if (j != 0) {
										hsql.append(",");
									}
									hsql.append("?");
								}
							}
							hsql.append(")");
							break;
						}
					}
				}
			}
		}
		// 排序
		Field[] fields = masterClazz.getDeclaredFields();
		String orderField = null;
		if (fields != null && fields.length > 0) {
			orderField = fields[0].getName();
		}
		if (orderField != null) {
			hsql.append(" order by " + alias[0] + "." + orderField + " asc");
		}
		return hsql.toString();

	}

	// 单表查询
	public List<E> findByFields(Class<?> clazz, SearchBean searchBean) throws DaoException {
		Session session = HibernateSessionFactory.getSession();
		try {
			StringBuffer hsql = new StringBuffer();
			hsql.append(" from " + clazz.getSimpleName());
			if (searchBean.getFields().length > 0) {
				hsql.append(" where ");
			}
			for (int i = 0; i < searchBean.getFields().length; i++) {
				FieldBean fieldBean = searchBean.getFields()[i];
				if (i != 0) {
					hsql.append(" and ");
				}
				hsql.append(fieldBean.getName());
				if (fieldBean.isLike()) {
					hsql.append(" like ? ");
				} else {
					hsql.append(" = ? ");
				}
			}
			Query query = (Query) session.createQuery(hsql.toString());
			for (int i = 0; i < searchBean.getFields().length; i++) {
				FieldBean field = searchBean.getFields()[i];
				if (field.getValue() instanceof String) {
					if (field.isLike()) {
						query.setString(i, "%" + (String) field.getValue() + "%");
					} else {
						query.setString(i, (String) field.getValue());
					}
				} else if (field.getValue() instanceof Integer) {
					query.setInteger(i, (Integer) field.getValue());
				}
			}
			PaginationBean page = searchBean.getPage();
			if (page != null) {
				query.setFirstResult((page.getCurrentPageNum() - 1) * PaginationBean.pageSize);
				query.setMaxResults(PaginationBean.pageSize);
			}
			return query.list();
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	public int count(Class<?> clazz, SearchBean searchBean) throws DaoException {
		Session session = HibernateSessionFactory.getSession();
		try {
			StringBuffer hsql = new StringBuffer();
			hsql.append(" select count(*) from " + clazz.getSimpleName());
			if (searchBean.getFields().length > 0) {
				hsql.append(" where ");
			}
			for (int i = 0; i < searchBean.getFields().length; i++) {
				FieldBean fieldBean = searchBean.getFields()[i];
				if (i != 0) {
					hsql.append(" and ");
				}
				hsql.append(fieldBean.getName());
				if (fieldBean.isLike()) {
					hsql.append(" like ? ");
				} else {
					hsql.append(" = ? ");
				}
			}
			Query query = (Query) session.createQuery(hsql.toString());
			for (int i = 0; i < searchBean.getFields().length; i++) {
				FieldBean field = searchBean.getFields()[i];
				if (field.getValue() instanceof String) {
					if (field.isLike()) {
						query.setString(i, "%" + (String) field.getValue() + "%");
					} else {
						query.setString(i, (String) field.getValue());
					}
				} else if (field.getValue() instanceof Integer) {
					query.setInteger(i, (Integer) field.getValue());
				}
			}
			PaginationBean page = searchBean.getPage();
			if (page != null) {
				query.setFirstResult((page.getCurrentPageNum() - 1) * PaginationBean.pageSize);
				query.setMaxResults(PaginationBean.pageSize);
			}
			return ((Long) query.uniqueResult()).intValue();
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
	}

	public Integer[] findChildDept(int deptId) throws DaoException {
		DeptDAO deptDAO = new DeptDAO();
		List<Department> depts = deptDAO.findChildren(deptId);
		List<Integer> deptList = new ArrayList<Integer>();
		deptList.add(new Integer(deptId));
		if (depts != null) {
			for (Department dept : depts) {
				deptList.add(dept.getDeptId());
			}
		}
		return deptList.toArray(new Integer[deptList.size()]);
	}
	
	public List<E> findBySQL(String sql,Class<?>[] classes) throws DaoException{
		Session session = HibernateSessionFactory.getSession();
		List<E> list = null;
		try {
			SQLQuery query =  session.createSQLQuery(sql);
			for (Class<?> clazz : classes) {
				query.addEntity(clazz);
			}
			list = query.list();
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			HibernateSessionFactory.closeSession();
		}
		return list;
	}
	
	public List<E> findBySQL(String sql) throws DaoException {
		Session session = HibernateSessionFactory.getSession();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			Connection conn = session.connection();
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery(sql);
		} catch (Exception e) {
			throw new DaoException(e);
		} finally {
			try {
				rs.close();
				ps.close();
			} catch (SQLException e) {
				throw new DaoException(e);
			}
			HibernateSessionFactory.closeSession();
		}
		return null;
	}
}
